﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Configuration;

namespace QLBH.DAO
{
    class Connection_Sql
    {
        static SqlConnection _con;
        static SqlDataAdapter _adt;
        static SqlCommand _comd;

        public static SqlCommand Comd
        {
            get { return Connection_Sql._comd; }
            set { Connection_Sql._comd = value; }
        }
        static String _stringCon = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
        static DataTable _dtable;

        public static DataTable Dtable
        {
            get { return Connection_Sql._dtable; }
            set { Connection_Sql._dtable = value; }
        }
        public static SqlConnection StartConnect()
        {
            try
            {
                _con = new SqlConnection(_stringCon);
                return _con;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Lỗi kết nối " + ex.Message, "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            return _con;
        }

        public static SqlConnection CloseConnection()
        {
            _con.Close();
            return _con;
        }
        public static int Insert(string query, IDictionary<string, string> d, bool isStored)
        {
            int affectedrows;
            try
            {
                _con.Open();
                _comd = new SqlCommand();
                _comd.CommandText = query;
                if (isStored)
                    _comd.CommandType = CommandType.StoredProcedure;
                _comd.Connection = _con;

                if (d != null)
                {
                    IDictionaryEnumerator en = (IDictionaryEnumerator)d.GetEnumerator();
                    while (en.MoveNext())
                    {
                        SqlParameter param = new SqlParameter();
                        param.ParameterName = en.Key.ToString();
                        param.Value = en.Value.ToString();
                        _comd.Parameters.Add(param);

                    }
                    _comd.Parameters.Add("@Thongbao", SqlDbType.NVarChar, 500).Direction = ParameterDirection.Output;
                }
                affectedrows = _comd.ExecuteNonQuery();
                string thongbao = _comd.Parameters["@Thongbao"].Value.ToString();
//                 if (thongbao.Length != 0)
//                 {
//                     //MessageBox.Show(thongbao);
//                 }
            }
            catch (System.Exception ex)
            {
                throw new Exception("Lỗi Insert" + ex.Message);

            }
            finally
            {
                _con.Close();
            }
            return affectedrows;
        }
        public static int Update(string query, IDictionary<string, string> d, bool isStored)
        {
            int affectedrows;
            try
            {
                _con.Open();
                _comd = new SqlCommand();
                _comd.CommandText = query;
                if (isStored)
                    _comd.CommandType = CommandType.StoredProcedure;
                _comd.Connection = _con;

                if (d != null)
                {
                    IDictionaryEnumerator en = (IDictionaryEnumerator)d.GetEnumerator();
                    while (en.MoveNext())
                    {
                        SqlParameter param = new SqlParameter();
                        param.ParameterName = en.Key.ToString();
                        param.Value = en.Value.ToString();
                        _comd.Parameters.Add(param);
                    }

                }
                affectedrows = _comd.ExecuteNonQuery();
                if (affectedrows > 0)
                    MessageBox.Show("Update thành công!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (System.Exception ex)
            {
                throw new Exception("Lỗi Insert" + ex.Message);

            }
            finally
            {
                _con.Close();
            }
            return affectedrows;
        }
        public static int Delete(string query, IDictionary<string, string> d, bool isStored)
        {
            int affectedrows;
            try
            {
                _con.Open();
                _comd = new SqlCommand();
                _comd.CommandText = query;
                if (isStored)
                    _comd.CommandType = CommandType.StoredProcedure;
                _comd.Connection = _con;

                if (d != null)
                {
                    IDictionaryEnumerator en = (IDictionaryEnumerator)d.GetEnumerator();
                    while (en.MoveNext())
                    {
                        SqlParameter param = new SqlParameter();
                        param.ParameterName = en.Key.ToString();
                        param.Value = en.Value.ToString();
                        _comd.Parameters.Add(param);

                    }

                }
                affectedrows = _comd.ExecuteNonQuery();

            }
            catch (System.Exception ex)
            {
                throw new Exception("Lỗi DELETE" + ex.Message);

            }
            finally
            {
                _con.Close();
            }
            return affectedrows;
        }
        //thuc hien cho cau lenh select
        public static DataTable GetDatatable(String sql, bool properties)
        {
            try
            {
                if (properties)
                {
                    _adt = new SqlDataAdapter(sql, _con);
                    _dtable = new DataTable();
                    _adt.Fill(_dtable);
                    return _dtable;
                }
                else
                {
                    SqlDataAdapter adapter = new SqlDataAdapter(sql, _con);
                    _dtable = new DataTable();
                    adapter.Fill(_dtable);
                    return _dtable;

                }
            }
            catch (System.Exception ex)
            {
                throw new Exception("Lỗi Getdata" + ex.Message);
            }
        }

    }
}
